package com.example.farmer.Database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.farmer.Entity.CirclePost;
import com.example.farmer.Entity.ClassPost;

import java.util.ArrayList;
import java.util.List;

public class ClassPostDatabase extends SQLiteOpenHelper {
    private static final String[] COLUMN_NAMES = {"id","image_uri","title"};
    private static ClassPostDatabase instance;
    private static final String DATABASE_NAME = "farmer_class.db"; // 数据库名称
    private static final int DATABASE_VERSION = 1; // 数据库版本

    // 表名和列名
    private static final String TABLE_NAME = "class_posts";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_TITLE = "title";
    private static final String COLUMN_CONTENT = "content";
    private static final String COLUMN_IMAGE_URI = "image_uri";
    private static final String COLUMN_TYPE = "type";
    private static final String COLUMN_ADDRESS = "address";
    private static final String COLUMN_NUMBER = "userNumber";

    // 创建表的 SQL 语句
    private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_TITLE + " TEXT,"
            + COLUMN_CONTENT + " TEXT,"
            + COLUMN_IMAGE_URI + " TEXT,"
            + COLUMN_TYPE + " TEXT,"
            + COLUMN_ADDRESS + " TEXT,"
            + COLUMN_NUMBER + " TEXT"
            + ")";

    public ClassPostDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static synchronized ClassPostDatabase getInstance(Context context) {
        if (instance == null) {
            instance = new ClassPostDatabase(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 当数据库版本变化时，删除旧表并创建新表
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    // 数据库操作方法，如insert, update, delete, query等


    //全部查询
    @SuppressLint("Range")
    public List<ClassPost> getAllClassPosts() {
        List<ClassPost> classPostsList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {
                COLUMN_ID,
                COLUMN_TITLE,
                COLUMN_CONTENT,
                COLUMN_IMAGE_URI,
                COLUMN_TYPE,
                COLUMN_ADDRESS
        };

        try (Cursor cursor = db.query(
                TABLE_NAME, // 表名
                columns, // 需要查询的所有列
                null, // 查询条件，查询所有记录，所以使用 null
                null, // 查询条件的参数，因为没有查询条件，所以使用 null
                null, // GROUP BY
                null, // HAVING
                COLUMN_ID + " DESC" // ORDER BY，根据需要排序，这里示例为按 ID 降序
        )) {
            while (cursor.moveToNext()) {
                // 遍历查询结果
                int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
                String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
                String content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
                String imageUri = cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_URI));
                String type = cursor.getString(cursor.getColumnIndex(COLUMN_TYPE));
                String address = cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS));

                // 创建 ClassPost 对象并添加到列表中
                classPostsList.add(new ClassPost(id, title, content, imageUri, type, address));
            }
        }
        // 确保关闭数据库
        db.close();
        return classPostsList;
    }
    //根据类型查找
    @SuppressLint("Range")
    public List<ClassPost> getPostsByType(String type) {
        // 查询特定类型的帖子列表
        List<ClassPost> classPostList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        try (Cursor cursor = db.query( // 执行查询
                TABLE_NAME, // 表名
                COLUMN_NAMES, // 列名数组
                COLUMN_TYPE + " = ?", // 查询条件，假设有一个列名为 "type"
                new String[]{type}, // 查询条件的参数
                null, // GROUP BY
                null, // HAVING
                null // ORDER BY
        )) {
            while (cursor.moveToNext()) {
                // 遍历查询结果
                int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));

                String imageUri = cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_URI));
                String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
                // 创建 ClassPost 对象并添加到列表中
                classPostList.add(new ClassPost(id, imageUri,title));
            }
            db.close();
        }
        return classPostList;
    }

    //根据标题查询
    @SuppressLint("Range")
    public ClassPost getClassPostByTitle(String title) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME,
                null, // 选择所有列
                COLUMN_TITLE + "=?",
                new String[]{title},
                null, null, null
        );

        if (cursor != null && cursor.moveToFirst()) {
            int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
            String content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
            String imageUri = cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_URI));
            String type = cursor.getString(cursor.getColumnIndex(COLUMN_TYPE));
            String  address= cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS));
            // 其他需要的字段...

            cursor.close();
            db.close();
            return new ClassPost(id, title, content, imageUri,type,address); // 根据实际情况构造ClassPost对象
        } else {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
            return null;
        }
    }

    public long insertPost(String title, String content, String type, String address, String imageUri) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, title);
        values.put(COLUMN_CONTENT, content);
        values.put(COLUMN_IMAGE_URI, imageUri);
        values.put(COLUMN_TYPE, type);
        values.put(COLUMN_ADDRESS, address);

        // 插入数据并返回操作成功与否
        long result = db.insert(TABLE_NAME, null, values);
        return result;
    }

    public void deletePost(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
    }
}
